/**
 * Class:   ParticipantManager
 * 
 * Methods:
 * 
 *      boolean insertParticipant( Participant participant );
 *      
 *      ArrayList<Participant> searchParticipant( int activityID );
 *      
 *      void deleteParticipantWithNoActivity();
 *      
 *      int getParticipantID( Participant participant );
 * 
 * Remarks:
 * 
 * 		This class should not be touched by the controllers.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import models.Participant;

public class ParticipantManager extends DatabaseManager {
    
    /**
     * Constructors
     */
    
    public ParticipantManager() {}
    
    
    
    /**
     *  Insert Participant
     *  	- inserts an participant into the participant database
     *  
     *  Parameters
     *		- Participant participant: the participant to be added
     *	
     *	Returns
     *		- boolean true: participant is successfully inserted
     * 		- boolean false: insertion failed
     */
    boolean insertParticipant( Participant participant ) {
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
    		
    		connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "INSERT INTO `participant`" +
    											   " (`participantName`," +
    											   " `courseDegree`) " +
    											   "VALUES (?, ?)" );

    		preparedStatement.setString( 1, participant.getName() );
    		preparedStatement.setString( 2, participant.getCourseDegree() );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getParticipantID( participant ) != 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
	
	
	
	/**
	 *  Search Participant
	 *  	- gets all participants in a certain activity
	 *  	  from the participant database
	 *  
	 *  Parameters
	 *		- int activityID: the ID of the activity the participants
	 *						  are from 
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no participants found
	 * 		- ArrayList<String>.size() > 0: participants found
	 */
	ArrayList<Participant> searchParticipant( int activityID ) {
		
		/* Declaration of Local Variables */
		ArrayList<Participant> participantList = new ArrayList<Participant>();
		
		try {
    		/* Declaration of Block Variables */
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			resultSet = statement
						.executeQuery( "SELECT * FROM `participant` WHERE" +
									   " `participantID` IN (SELECT" +
									   " `participantID` FROM" +
									   " `v_activityParticipant` WHERE" +
									   " `activityID` = " + activityID + ")" );
			
			while ( resultSet.next() ) {
				participantList.add( new Participant
									( resultSet.getString( "participantName" ),
									  resultSet.getString( "courseDegree" ) ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return participantList;
	}
	
	
	
	/**
	 *  View Participant
	 *  	- gets all participants from the participant database
	 *  
	 *  Parameters
	 *		- n/a
	 *	
	 *	Returns
	 *		- ArrayList<String>.size() == 0: no participants found
	 * 		- ArrayList<String>.size() > 0: participants found
	 */
	public ArrayList<Participant> viewParticipant() {
		
		/* Declaration of Local Variables */
		ArrayList<Participant> participantList = new ArrayList<Participant>();
		
		try {
    		/* Declaration of Block Variables */
			Statement statement;
			ResultSet resultSet;
			
			connect();
			
			statement = getConnection().createStatement();
			
			resultSet = statement
						.executeQuery( "SELECT * FROM `participant`" );
			
			while ( resultSet.next() ) {
				participantList.add( new Participant
									( resultSet.getString( "participantName" ),
									  resultSet.getString( "courseDegree" ) ) );
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
		}
		return participantList;
	}
    
    
    
    /**
     *  Delete Participant with No Activity
     *  	- deletes a participants with no corresponding 
     *  	  activity in the v_activityParticipant database
     *  
     *  Parameters
     *		- n/a
     *	
     *	Returns
     *		- n/a
     */
    void deleteParticipantWithNoActivity() {
    	try {
    		/* Declaration of Block Variables */
    		Statement statement;
    		connect();
    		
    		statement = getConnection().createStatement();
    		statement.executeUpdate( "DELETE FROM `participant` WHERE" +
    								 " `participantID` NOT IN" +
    								 " (SELECT DISTINCT `participantID`" +
    								 " FROM `v_activityParticipant`)" );
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    }
    
    
    
    /**
     *  Get Participant ID
     *  	- finds the ID of the participant in the participant database
     *  
     *  Parameters
     *		- Participant participant: the participant to be found
     *	
     *	Returns
     *		- non-zero int: participant ID found
     * 		- int 0: participant not found
     */
    int getParticipantID( Participant participant ) {
    	try {
    		/* Declaration of Block Variables */
    		int participantID = 0;
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		resultSet = statement
    					.executeQuery( "SELECT `participantID`" +
    								   " FROM `participant` WHERE" +
    								   " `participantName` = \"" +
    								   participant.getName() + "\" AND " +
    								   "`courseDegree` = \"" +
    								   participant.getCourseDegree() + "\"" );
    		
    		while ( resultSet.next() ) {
    			participantID = resultSet.getInt( "participantID" );
    		}
    		
    		disconnect();
    		return participantID;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return 0;
    }
}
